

CREATE TABLE Categories
  (
    id            INTEGER NOT NULL ,
    name          VARCHAR2 (40) NOT NULL ,
    parent_id     INTEGER 
  ) ;
  
  ALTER TABLE Categories ADD CONSTRAINT Categories_PK PRIMARY KEY ( id ) ;
  ALTER TABLE Categories ADD CONSTRAINT Categories_FK FOREIGN KEY (parent_id ) REFERENCES Categories(id);
  
  
 
CREATE TABLE Producers
  (
    id           INTEGER ,
    description  VARCHAR2 (50) 
  ) ;
ALTER TABLE Producers ADD CONSTRAINT Producers_PK PRIMARY KEY ( id ) ;




CREATE TABLE Brands
( id INTEGER NOT NULL , 
  description VARCHAR2 (50)
);
ALTER TABLE Brands ADD CONSTRAINT Brands_PK PRIMARY KEY ( id ) ; 


CREATE TABLE Materials
( id INTEGER NOT NULL , description VARCHAR2 (50)
);
ALTER TABLE Materials ADD CONSTRAINT Materials_PK PRIMARY KEY ( id ) ;


CREATE TABLE Users
  (
    id           INTEGER NOT NULL ,
    email        VARCHAR2 (50) ,
    name         VARCHAR2 (50) ,
    phone        VARCHAR2 (50) ,
    gender       CHAR,
    bulletin     CHAR,
    login_name   VARCHAR2 (50) NOT NULL ,
    password     VARCHAR2 (50) NOT NULL ,
    account_type CHAR NOT NULL
  ) ;
  
ALTER TABLE Users ADD CONSTRAINT Users_PK PRIMARY KEY ( id ) ;
Alter table Users drop column gender;
alter table users drop column bulletin;
alter table users add (gender VARCHAR2(20));
alter table users add (bulletin Varchar2(20));
ALTER TABLE Users ADD CONSTRAINT login_name_unique UNIQUE (login_name);

CREATE TABLE Items
  (
    id                     INTEGER NOT NULL ,
    description            VARCHAR2 (100) NOT NULL ,
    price                  NUMBER(*,2) NOT NULL ,
    promotional_price      NUMBER(*,2) ,
    whole_sale_price       NUMBER(*,2) ,
    supplier_id            INTEGER NOT NULL,
    producer_id            INTEGER NOT NULL,
    brand_id               INTEGER NOT NULL
  ) ;
  
ALTER TABLE Items ADD CONSTRAINT Items_PK PRIMARY KEY ( id ) ;
ALTER TABLE Items ADD CONSTRAINT Items_FK_brand FOREIGN KEY (brand_id ) REFERENCES Brands(id);
ALTER TABLE Items ADD CONSTRAINT Items_FK_supplier FOREIGN KEY (supplier_id ) REFERENCES Users(id);
ALTER TABLE Items ADD CONSTRAINT Items_FK_producer FOREIGN KEY (producer_id ) REFERENCES Producers(id);
  
  
CREATE TABLE Colors
  (
    id   INTEGER NOT NULL ,
    name VARCHAR2 (50) NOT NULL ,
    path VARCHAR2 (4000) NOT NULL ,
    code VARCHAR2 (20) NOT NULL
  ) ;
  
ALTER TABLE Colors ADD CONSTRAINT Colors_PK PRIMARY KEY ( id ) ;

CREATE TABLE Couriers
  (
    id    INTEGER NOT NULL ,
    name  VARCHAR2 (100) ,
    phone VARCHAR2 (20)
  ) ;
  
ALTER TABLE Couriers ADD CONSTRAINT Couriers_PK PRIMARY KEY ( id ) ;

CREATE TABLE Figures
  (
    item_id  INTEGER NOT NULL ,
    path     VARCHAR2 (4000) NOT NULL
  ) ;
  
ALTER TABLE Figures ADD CONSTRAINT Figures_PK PRIMARY KEY ( item_id, path ) ;
ALTER TABLE Figures ADD color_id  INTEGER NOT NULL;
ALTER TABLE Figures ADD CONSTRAINT Figures_FK_color FOREIGN KEY (color_id) REFERENCES colors(id);
--Alter table fixgures drop column is_main;
--ALTER TABLE Figures ADD is_main CHAR;

CREATE TABLE Item_materials
  (
    item_id      INTEGER NOT NULL ,
    material_id  INTEGER NOT NULL ,
    percentage   INTEGER NOT NULL 
  ) ;
  
ALTER TABLE Item_Materials ADD CONSTRAINT Item_Materials_PK PRIMARY KEY ( item_id, material_id) ;



CREATE TABLE Sizes
( id INTEGER NOT NULL ,
name VARCHAR2 (10) NOT NULL) ;

ALTER TABLE Sizes ADD CONSTRAINT Sizes_PK PRIMARY KEY ( id ) ;


CREATE TABLE Clothing_Inventory
  (
    inventory_id INTEGER NOT NULL ,
    item_id      INTEGER NOT NULL ,
    size_id      INTEGER NOT NULL ,
    color_id     INTEGER NOT NULL ,
    count        INTEGER
  ) ;
  
  
ALTER TABLE Clothing_Inventory ADD CONSTRAINT Clothing_Inventory_PK PRIMARY KEY ( inventory_id ) ;
ALTER TABLE Clothing_Inventory ADD CONSTRAINT Clothing_Inventory_FK_item FOREIGN KEY (item_id ) REFERENCES Items(id);
ALTER TABLE Clothing_Inventory ADD CONSTRAINT Clothing_Inventory_FK_size FOREIGN KEY (size_id ) REFERENCES Sizes(id);
ALTER TABLE Clothing_Inventory ADD CONSTRAINT Clothing_Inventory_FK_color FOREIGN KEY (color_id ) REFERENCES Colors(id);


CREATE TABLE Item_categories
  (
    item_id       INTEGER NOT NULL ,
    category_id   INTEGER NOT NULL 
  ) ;
ALTER TABLE Item_categories ADD CONSTRAINT Item_categories_PK PRIMARY KEY ( item_id, category_id) ;
ALTER TABLE Item_categories ADD CONSTRAINT Item_categories_FK_item FOREIGN KEY (item_id ) REFERENCES Items(id);
ALTER TABLE Item_categories ADD CONSTRAINT Item_categories_FK_category FOREIGN KEY (category_id ) REFERENCES Categories(id);

CREATE TABLE Orders
  (
    order_number  INTEGER NOT NULL ,
    date_time     DATE NOT NULL ,
    email         VARCHAR2 (50) NOT NULL ,
    name          VARCHAR2 (50) NOT NULL,
    phone_number  VARCHAR2 (20) NOT NULL,
    courier_id    INTEGER NOT NULL,
    address_line1 VARCHAR2 (100) NOT NULL,
    address_line2  VARCHAR2 (100), 
    city_id        INTEGER  NOT NULL ,
    postal_code VARCHAR2 (30) ,
    region_id      INTEGER NOT NULL,
    country_id     INTEGER NOT NULL
  ) ;
  
ALTER TABLE Orders ADD CONSTRAINT Orders_PK PRIMARY KEY ( order_number ) ;
ALTER TABLE Orders ADD CONSTRAINT Orders_FK_city FOREIGN KEY (city_id ) REFERENCES Cities(id);
ALTER TABLE Orders ADD CONSTRAINT Orders_FK_region FOREIGN KEY (region_id ) REFERENCES Regions(id);
ALTER TABLE Orders ADD CONSTRAINT Orders_FK_country FOREIGN KEY (country_id ) REFERENCES Countries(id);

CREATE TABLE Ordered_Inventory
  (
    order_number           INTEGER NOT NULL ,
    inventory_id           INTEGER NOT NULL ,
    count                  INTEGER NOT NULL 
  ) ;
ALTER TABLE Ordered_Inventory ADD CONSTRAINT Ordered_Inventory_PK PRIMARY KEY ( order_number, inventory_id) ;
ALTER TABLE Ordered_Inventory ADD CONSTRAINT Ordered_Inventory_FK_order FOREIGN KEY (order_number ) REFERENCES Orders(order_number);
ALTER TABLE Ordered_Inventory ADD CONSTRAINT Ordered_Inventory_FK_inventory FOREIGN KEY (inventory_id ) REFERENCES Clothing_Inventory(inventory_id);


CREATE TABLE Countries
  (
    id   INTEGER NOT NULL ,
    name VARCHAR2 (50) NOT NULL
  ) ;
ALTER TABLE Countries ADD CONSTRAINT Countries_PK PRIMARY KEY ( id ) ;


CREATE TABLE Regions
  (
    id           INTEGER NOT NULL ,
    name         VARCHAR2 (50) NOT NULL ,
    country_id   INTEGER NOT NULL
  ) ;
  
  ALTER TABLE Regions ADD CONSTRAINT Regions_PK PRIMARY KEY ( id ) ;
  ALTER TABLE Regions ADD CONSTRAINT Regions_FK FOREIGN KEY (country_id ) REFERENCES Countries(id);



CREATE TABLE Cities
  (
    id                   INTEGER NOT NULL ,
    name                 VARCHAR2 (50) NOT NULL ,
    region_id            INTEGER NOT NULL 
  ) ;
  
ALTER TABLE Cities ADD CONSTRAINT Cities_PK PRIMARY KEY ( id ) ;
ALTER TABLE Cities ADD CONSTRAINT Cities_FK FOREIGN KEY (region_id ) REFERENCES Regions(id);



CREATE TABLE Locations
  (
    id            INTEGER NOT NULL ,
    address_line1 VARCHAR2 (100) NOT NULL ,
    address_line2 VARCHAR2 (100) ,
    city_id       INTEGER NOT NULL ,
    postal_code   VARCHAR2 (20) 
  ) ;
  
ALTER TABLE Locations ADD CONSTRAINT Locations_PK PRIMARY KEY ( id ) ;
ALTER TABLE Locations ADD CONSTRAINT Locations_FK FOREIGN KEY (city_id ) REFERENCES Cities(id);


CREATE TABLE Entity_Locations
  (
    entity_id              INTEGER NOT NULL ,
    location_id            INTEGER NOT NULL 
  ) ;
ALTER TABLE Entity_Locations ADD CONSTRAINT Entity_Locations_PK PRIMARY KEY (entity_id, location_id) ;
ALTER TABLE Entity_Locations ADD CONSTRAINT Entity_Locations_FK FOREIGN KEY (location_id ) REFERENCES Locations(id);

CREATE TABLE Presents
  (
      id INTEGER NOT NULL,
      description VARCHAR2(4000) NOT NULL,
      figure_path VARCHAR2(4000),
      count INTEGER NOT NULL
  );
ALTER TABLE Presents ADD CONSTRAINT Presents_PK PRIMARY KEY (id);


CREATE TABLE Item_Presents 
  (
    item_id INTEGER NOT NULL,
    present_id INTEGER NOT NULL
  );  
ALTER TABLE Item_Presents ADD CONSTRAINT Item_Presents_item_FK FOREIGN KEY(item_id) REFERENCES Items(id);
ALTER TABLE Item_Presents ADD CONSTRAINT Item_Presents_present_FK FOREIGN KEY(present_id) REFERENCES Presents(id);

CREATE TABLE Features
  (
    item_id INTEGER NOT NULL,
    description VARCHAR2(4000) NOT NULL
  );
  ALTER TABLE Features ADD CONSTRAINT Features_item_FK FOREIGN KEY(item_id) References Items(id);


CREATE TABLE Additional_Sizes
  (
    item_id INTEGER NOT NULL,
    size_id INTEGER NOT NULL,
    additional_size_description VARCHAR2(4000)
  );
  
ALTER TABLE Additional_Sizes ADD CONSTRAINT Additional_Sizes_item_FK FOREIGN KEY(item_id) REFERENCES Items(id);
ALTER TABLE Additional_Sizes ADD CONSTRAINT Additional_Sizes_size_FK FOREIGN KEY(size_id) REFERENCES Sizes(id);


CREATE SEQUENCE Presents_SEQ START WITH 1;

CREATE TRIGGER before_insert_presents
BEFORE INSERT ON Presents FOR EACH ROW
BEGIN
SELECT Presents_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;


CREATE SEQUENCE Categories_SEQ START WITH 1;

CREATE TRIGGER before_insert_categories
BEFORE INSERT ON Categories FOR EACH ROW
BEGIN
SELECT Categories_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;

--drop trigger before_insert_categories;

CREATE SEQUENCE Items_SEQ START WITH 1;

CREATE TRIGGER before_insert_items
BEFORE INSERT ON Items FOR EACH ROW
BEGIN
SELECT Items_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;

CREATE SEQUENCE Materials_SEQ START WITH 1;

CREATE TRIGGER before_insert_materials
BEFORE INSERT ON Materials FOR EACH ROW
BEGIN
SELECT Materials_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;


CREATE SEQUENCE Colors_SEQ START WITH 1;

CREATE TRIGGER before_insert_colors
BEFORE INSERT ON Colors FOR EACH ROW
BEGIN
SELECT Colors_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;


CREATE SEQUENCE Inventory_SEQ START WITH 1;

CREATE TRIGGER before_insert_inventory
BEFORE INSERT ON Clothing_Inventory FOR EACH ROW
BEGIN
SELECT Inventory_SEQ.NEXTVAL
INTO :new.inventory_id
FROM dual;
END;


CREATE SEQUENCE Sizes_SEQ START WITH 1;

CREATE TRIGGER before_insert_sizes
BEFORE INSERT ON Sizes FOR EACH ROW
BEGIN
SELECT Sizes_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;

CREATE SEQUENCE Brands_SEQ START WITH 1;

CREATE TRIGGER before_insert_Brands
BEFORE INSERT ON Brands FOR EACH ROW
BEGIN
SELECT Brands_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;

CREATE SEQUENCE Countries_SEQ START WITH 1;

CREATE TRIGGER before_insert_Countries
BEFORE INSERT ON Countries FOR EACH ROW
BEGIN
SELECT Countries_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;

CREATE SEQUENCE Regions_SEQ START WITH 1;

CREATE TRIGGER before_insert_regions
BEFORE INSERT ON Regions FOR EACH ROW
BEGIN
SELECT Regions_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;

CREATE SEQUENCE Cities_SEQ START WITH 1;

CREATE TRIGGER before_insert_cities
BEFORE INSERT ON Cities FOR EACH ROW
BEGIN
SELECT Cities_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;


CREATE SEQUENCE Locations_SEQ START WITH 1;

CREATE TRIGGER before_insert_locations
BEFORE INSERT ON Locations FOR EACH ROW
BEGIN
SELECT Locations_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;

CREATE SEQUENCE Entities_SEQ START WITH 1 ORDER CACHE 10;

CREATE TRIGGER before_insert_users
BEFORE INSERT ON Users FOR EACH ROW
BEGIN
SELECT Entities_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;

CREATE TRIGGER before_insert_producers
BEFORE INSERT ON Producers FOR EACH ROW
BEGIN
SELECT Entities_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;


CREATE TRIGGER before_insert_couriers
BEFORE INSERT ON Couriers FOR EACH ROW
BEGIN
SELECT Entities_SEQ.NEXTVAL
INTO :new.id
FROM dual;
END;


CREATE SEQUENCE unique_number_seq START WITH 1;
SELECT unique_number_seq.nextval FROM dual